﻿using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Excel;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using TamoCRM.Core;
using TamoCRM.Core.Data;
using TamoCRM.Domain.Activity;
using TamoCRM.Domain.ContactDuplicates;
using TamoCRM.Domain.ContactLevelInfos;
using TamoCRM.Domain.Contacts;
using TamoCRM.Domain.ImportExcels;
using TamoCRM.Domain.Phones;
using TamoCRM.Services.Activity;
using TamoCRM.Services.ContactDuplicates;
using TamoCRM.Services.ContactLevelInfos;
using TamoCRM.Services.Contacts;
using TamoCRM.Services.ImportExcels;
using TamoCRM.Core.Commons.Extensions;
using TamoCRM.Services.Phones;

namespace TamoCRM.ImportExcel.Library
{
    public class ImportProcess : IDisposable
    {
        #region Fields
        private DataTable dtPhones;
        private DataTable dtContacts;
        private DataTable dtContactTmps;
        private DataTable dtContactLevels;
        private DataTable dtObjectChanges;
        public ImportExcelInfo ImportInfo { get; set; }
        private readonly HashSet<string> internalPhoneAndEmails = new HashSet<string>();
        private readonly Dictionary<int, ContactDuplicateInfo> duplicateContactIds = new Dictionary<int, ContactDuplicateInfo>();
        #endregion

        public ImportProcess(ImportExcelInfo importInfo)
        {
            ImportInfo = importInfo;
            dtContacts = CreateContact();
            dtPhones = CreatePhoneTable();
            dtContactLevels = CreateContactLevel();
            dtContactTmps = CreateContactTmpTable();
            dtObjectChanges = CreateObjectChangeTable();
        }

        public void Start()
        {
            var watch = new Stopwatch();
            watch.Start();
            Console.WriteLine("Processing file: " + ImportInfo.FilePath);
            DoImport(ImportInfo.FilePath);
            watch.Stop();
            Console.WriteLine("Processed file: " + ImportInfo.FilePath + " in: " + watch.ElapsedMilliseconds);
        }

        private void DoImport(string file)
        {
            try
            {
                //Create activity log
                var activity = new ActivityLogInfo
                                   {
                                       CreatedDate = DateTime.Now,
                                       CreatedBy = ImportInfo.UserId,
                                       FunctionId = (int)LogFunctionType.ImportExcel,
                                       FunctionType = (int)LogFunctionType.ImportExcel,
                                   };
                activity.Id = ActivityLogRepository.Create(activity);

                //End log
                using (var connection = new SqlConnection(ImportConfig.ConnectionString))
                {
                    connection.Open();

                    SqlTransaction transaction = null;
                    try
                    {
                        transaction = connection.BeginTransaction();
                        // Use one transaction to put all the data in the database
                        var watch = new Stopwatch();
                        watch.Start();
                        var contactIdentity = GetIdentity("Contacts", connection, transaction);
                        using (var stream = File.Open(file, FileMode.Open, FileAccess.Read))
                        {
                            using (var dr = ExcelReaderFactory.CreateOpenXmlReader(stream))
                            {
                                var rowIndex = 0;

                                while (dr.Read())
                                {
                                    if (rowIndex > 3)
                                    {
                                        #region Read excel row
                                        var code = dr[1] == null ? string.Empty : dr[1].ToString().Trim();
                                        var fullname = dr[2] == null ? string.Empty : dr[2].ToString().Trim();
                                        var birthday = dr[3] == null ? string.Empty : dr[3].ToString().Trim();
                                        var mobile1 = dr[4] == null ? string.Empty : Util.CleanAlphabetAndFirstZero(dr[4].ToString().Trim());
                                        var mobile2 = dr[5] == null ? string.Empty : Util.CleanAlphabetAndFirstZero(dr[5].ToString().Trim());
                                        var tel = dr[6] == null ? string.Empty : Util.CleanAlphabetAndFirstZero(dr[6].ToString().Trim());
                                        var email = dr[7] == null ? string.Empty : dr[7].ToString().Trim();
                                        var school = dr[8] == null ? string.Empty : dr[8].ToString().Trim();
                                        var schoolId = StaticData.GetSchoolId(school);
                                        var major = dr[9] == null ? string.Empty : dr[9].ToString().Trim();
                                        var majorId = StaticData.GetMajorId(major);
                                        var majorAll = major.ToLower() == "tatca" || major.ToLower() == "tất cả"
                                                           ? StaticData.GetAllMajor()
                                                           : StaticData.GetMajorId(major).ToString();
                                        var question = dr[10] == null ? string.Empty : dr[10].ToString().Trim();
                                        DateTime? registeredDate;
                                        if (dr[11] == null) registeredDate = null;
                                        else
                                        {
                                            try
                                            {
                                                registeredDate = (DateTime)dr[11];
                                            }
                                            catch
                                            {
                                                registeredDate = dr[11].ToDateTime();
                                            }
                                        }
                                        var enrollmentMark = dr[12] == null ? string.Empty : dr[12].ToString().Trim();
                                        var channel = StaticData.ParseChannel(enrollmentMark);
                                        var channelId = StaticData.GetChannelId(channel, ImportInfo.ChannelId);
                                        var educationalLevel = dr[13] == null ? string.Empty : dr[13].ToString().Trim();
                                        var educationLevelId = StaticData.GetEducationLevel(educationalLevel);
                                        var schoolGrad = dr[14] == null ? string.Empty : dr[14].ToString().Trim();
                                        var location = dr[15] == null ? string.Empty : dr[15].ToString().Trim();
                                        var locationId = StaticData.GetLocationId(location);
                                        var haveJob = dr[16] == null ? string.Empty : dr[16].ToString().Trim();
                                        var collaboratorName = dr[27] == null ? string.Empty : dr[27].ToString().Trim();
                                        DateTime? callDateDistributor;
                                        if (dr[28] == null) callDateDistributor = null;
                                        else
                                        {
                                            try
                                            {
                                                callDateDistributor = (DateTime)dr[28];
                                            }
                                            catch
                                            {
                                                callDateDistributor = dr[28].ToDateTime();
                                            }
                                        }
                                        var contactIdDb = 0;
                                        var contactTmpStatus = 0;
                                        var contactInfoDb = string.Empty;
                                        #endregion

                                        #region validate row
                                        if (string.IsNullOrEmpty(fullname) &&
                                            string.IsNullOrEmpty(mobile1) &&
                                            string.IsNullOrEmpty(mobile2) &&
                                            string.IsNullOrEmpty(email) &&
                                            string.IsNullOrEmpty(tel))
                                            continue;
                                        ImportInfo.RowIndex++;
                                        ImportInfo.TotalRow++;

                                        //Contact validation
                                        var error = Validate(mobile1, mobile2, tel, email);

                                        // haihm
                                        if (error == ContactError.EmailFormat)
                                        {
                                            email = null;
                                            error = ContactError.None;
                                        }
                                        if (error != ContactError.None)
                                        {
                                            ImportInfo.ErrorCount++;
                                            contactTmpStatus = (int)error;
                                        }
                                        else
                                        {
                                            //Check if contact is internal duplicate or not
                                            if (IsInternalDuplicate(mobile1, mobile2, tel, email))
                                            {
                                                contactTmpStatus = (int)ContactError.InternalDuplicate;
                                                ImportInfo.InternalDuplicateCount++;
                                            }
                                            else
                                            {
                                                //If not internal duplicate, add to hashtable data
                                                if (!string.IsNullOrEmpty(tel)) internalPhoneAndEmails.Add(tel);
                                                if (!string.IsNullOrEmpty(email)) internalPhoneAndEmails.Add(email);
                                                if (!string.IsNullOrEmpty(mobile1)) internalPhoneAndEmails.Add(mobile1);
                                                if (!string.IsNullOrEmpty(mobile2)) internalPhoneAndEmails.Add(mobile2);
                                                var check = CheckDuplicateProvider.Instance().IsDuplicate(mobile1, mobile2, tel, email, string.Empty, out contactIdDb, out contactInfoDb);
                                                if (check)
                                                {
                                                    ImportInfo.DuplicateCount++;
                                                    contactTmpStatus = (int)ContactError.Duplicate;
                                                }
                                            }

                                        }
                                        ImportInfo.CheckCount++;
                                        #endregion

                                        switch (contactTmpStatus)
                                        {
                                            case (int)ContactError.None:
                                                {
                                                    #region create contact row
                                                    var contactId = contactIdentity + rowIndex;
                                                    var contactRow = dtContacts.NewRow();
                                                    contactRow["Id"] = contactId;
                                                    contactRow["UserId"] = null;
                                                    contactRow["ConsultantUserId"] = ImportInfo.UserId;
                                                    contactRow["Code"] = code;
                                                    contactRow["Fullname"] = fullname;
                                                    contactRow["Email"] = email;
                                                    contactRow["Address"] = location;
                                                    contactRow["Gender"] = 0;
                                                    contactRow["TypeId"] = ImportInfo.TypeId;
                                                    contactRow["ChannelId"] = channelId;
                                                    contactRow["Draft"] = null;
                                                    contactRow["MajorId"] = majorId;
                                                    contactRow["SchoolId"] = schoolId;
                                                    contactRow["LevelId"] = ImportInfo.LevelId;
                                                    contactRow["CollectorId"] = ImportInfo.CollectorId;
                                                    contactRow["BranchId"] = ImportInfo.BranchId;
                                                    contactRow["EducationLevelId"] = educationLevelId;
                                                    contactRow["LocationId"] = locationId;
                                                    contactRow["StatusId"] = ImportInfo.Status;
                                                    contactRow["StatusCareId"] = 0;
                                                    contactRow["StatusMapId"] = 0;
                                                    contactRow["SchoolCare"] = schoolId;
                                                    contactRow["Notes"] = string.Empty;
                                                    contactRow["ImportId"] = ImportInfo.ImportId;
                                                    contactRow["RegisteredDate"] = registeredDate;
                                                    contactRow["Birth"] = birthday.ToDateTime();
                                                    contactRow["AppointmentDate"] = null;
                                                    contactRow["HandoverDate"] = null;
                                                    contactRow["CallDate"] = null;
                                                    contactRow["RecoveryDate"] = null;
                                                    contactRow["ReuseDate"] = null;
                                                    contactRow["IsDuplicate"] = (contactTmpStatus == (int)ContactError.Duplicate);
                                                    contactRow["CallDateDistributor"] = callDateDistributor;
                                                    if (ImportInfo.Status == (int)StatusType.HandoverToDistributor || 
                                                        ImportInfo.Status == (int)StatusType.ContactReuse)
                                                        contactRow["HandoverDistributorDate"] = DateTime.Now;
                                                    dtContacts.Rows.Add(contactRow);
                                                    #endregion

                                                    #region create phone row

                                                    if (!string.IsNullOrEmpty(mobile1))
                                                    {
                                                        var phoneRow = dtPhones.NewRow();
                                                        phoneRow["ContactId"] = contactId;
                                                        phoneRow["PhoneType"] = (int)PhoneType.HomePhone;
                                                        phoneRow["PhoneNumber"] = mobile1;
                                                        phoneRow["IsPrimary"] = 1;
                                                        dtPhones.Rows.Add(phoneRow);
                                                    }
                                                    if (!string.IsNullOrEmpty(mobile2))
                                                    {
                                                        var phoneRow = dtPhones.NewRow();
                                                        phoneRow["ContactId"] = contactId;
                                                        phoneRow["PhoneType"] = (int)PhoneType.MobilePhone;
                                                        phoneRow["PhoneNumber"] = mobile2;
                                                        phoneRow["IsPrimary"] = 0;
                                                        dtPhones.Rows.Add(phoneRow);
                                                    }
                                                    if (!string.IsNullOrEmpty(tel))
                                                    {
                                                        var phoneRow = dtPhones.NewRow();
                                                        phoneRow["ContactId"] = contactId;
                                                        phoneRow["PhoneType"] = (int)PhoneType.Tel;
                                                        phoneRow["PhoneNumber"] = tel;
                                                        phoneRow["IsPrimary"] = 0;
                                                        dtPhones.Rows.Add(phoneRow);
                                                    }

                                                    #endregion

                                                    #region create contact level row

                                                    var contactLelveRow = dtContactLevels.NewRow();
                                                    contactLelveRow["ContactId"] = contactId;
                                                    contactLelveRow["EducationLevels"] = educationLevelId;
                                                    contactLelveRow["CollaboratorName"] = collaboratorName;
                                                    contactLelveRow["HaveJob"] = haveJob.ToLower().Equals("rồi");
                                                    contactLelveRow["Majors"] = majorAll;
                                                    contactLelveRow["SchoolGrad"] = schoolGrad;
                                                    dtContactLevels.Rows.Add(contactLelveRow);

                                                    #endregion

                                                    #region create object changes
                                                    foreach (DataColumn col in dtContacts.Columns)
                                                    {
                                                        if (col.ColumnName != "Email" &&
                                                            col.ColumnName != "TypeId" &&
                                                            col.ColumnName != "LevelId" &&
                                                            col.ColumnName != "SchoolId" &&
                                                            col.ColumnName != "StatusId" &&
                                                            col.ColumnName != "ChannelId" &&
                                                            col.ColumnName != "BranchId")
                                                            continue;
                                                        var objChange = dtObjectChanges.NewRow();
                                                        objChange["ActivityId"] = activity.Id;
                                                        objChange["ObjectType"] = (int)LogObjectType.Contact;
                                                        objChange["ObjectId"] = contactId;
                                                        var propertyType = 0;
                                                        int? propertyValueInt = null;
                                                        string propertyValueString = null;
                                                        DateTime? propertyValueDateTime = null;
                                                        switch (col.ColumnName)
                                                        {
                                                            case "BranchId":
                                                                propertyType = (int)LogPropertyType.Contacts_Branch;
                                                                propertyValueInt = ImportInfo.BranchId;
                                                                break;
                                                            case "TypeId":
                                                                propertyType = (int)LogPropertyType.Contacts_Type;
                                                                propertyValueInt = ImportInfo.TypeId;
                                                                break;
                                                            case "LevelId":
                                                                propertyType = (int)LogPropertyType.Contacts_Level;
                                                                propertyValueInt = ImportInfo.LevelId;
                                                                break;
                                                            case "SchoolId":
                                                                propertyType = (int)LogPropertyType.Contacts_School;
                                                                propertyValueInt = schoolId;
                                                                break;
                                                            case "StatusId":
                                                                propertyType = (int)LogPropertyType.Contacts_Status;
                                                                propertyValueInt = ImportInfo.Status;
                                                                break;
                                                            case "ChannelId":
                                                                propertyType = (int)LogPropertyType.Contacts_Channel;
                                                                propertyValueInt = channelId;
                                                                break;
                                                            case "Email":
                                                                propertyType = (int)LogPropertyType.Contacts_Email;
                                                                propertyValueString = email;
                                                                break;
                                                        }
                                                        objChange["PropertyType"] = propertyType;
                                                        objChange["PropertyValueInt"] = propertyValueInt;
                                                        objChange["PropertyValueString"] = propertyValueString;
                                                        objChange["PropertyValueDateTime"] = propertyValueDateTime;
                                                        objChange["ChangedDate"] = DateTime.Now;
                                                        dtObjectChanges.Rows.Add(objChange);
                                                    }

                                                    #endregion
                                                }
                                                break;
                                            case (int)ContactError.Duplicate:
                                                if (ImportInfo.TypeId == (int)SourceType.MO ||
                                                    ImportInfo.TypeId == (int)SourceType.ReUse ||
                                                    (ImportInfo.TypeId == (int)SourceType.CC && ImportInfo.LevelId == (int)LevelType.L2))
                                                {
                                                    if (!contactIdDb.IsIntegerNull() && !duplicateContactIds.ContainsKey(contactIdDb))
                                                    {
                                                        duplicateContactIds.Add(contactIdDb, new ContactDuplicateInfo
                                                                                                 {
                                                                                                     ContactId = contactIdDb,
                                                                                                     CreatedDate = DateTime.Now,
                                                                                                     DuplicateInfo = contactInfoDb,
                                                                                                     LevelId = ImportInfo.LevelId,
                                                                                                     ImportId = ImportInfo.ImportId,
                                                                                                     SourceTypeId = ImportInfo.TypeId,
                                                                                                     ContactInfo = new ContactInfo
                                                                                                                       {
                                                                                                                           Tel = tel,
                                                                                                                           Code = code,
                                                                                                                           Email = email,
                                                                                                                           Mobile = mobile1,
                                                                                                                           Mobile2 = mobile2,
                                                                                                                           MajorId = majorId,
                                                                                                                           Address = location,
                                                                                                                           Fullname = fullname,
                                                                                                                           SchoolId = schoolId,
                                                                                                                           ChannelId = channelId,
                                                                                                                           SchoolCare = schoolId,
                                                                                                                           LocationId = locationId,
                                                                                                                           Birth = birthday.ToDateTime(),
                                                                                                                           RegisteredDate = registeredDate,
                                                                                                                           EducationLevelId = educationLevelId,
                                                                                                                       },
                                                                                                     ContactLevelInfo = new ContactLevelInfo
                                                                                                                            {
                                                                                                                                Majors = majorAll,
                                                                                                                                SchoolGrad = schoolGrad,
                                                                                                                                CollaboratorName = collaboratorName,
                                                                                                                                HaveJob = haveJob.ToLower().Equals("rồi"),
                                                                                                                                EducationLevels = educationLevelId.ToString(),
                                                                                                                            }
                                                                                                 });
                                                    }
                                                }
                                                break;
                                            default:
                                                {
                                                    #region create contact temp errors
                                                    var tmpRow = dtContactTmps.NewRow();
                                                    tmpRow["StudentId"] = code;
                                                    tmpRow["ImportId"] = ImportInfo.ImportId;
                                                    tmpRow["Fullname"] = fullname;
                                                    tmpRow["Birth"] = birthday;
                                                    tmpRow["Mobil1"] = mobile1;
                                                    tmpRow["Mobil2"] = mobile2;
                                                    tmpRow["Tel"] = tel;
                                                    tmpRow["Email"] = email;
                                                    tmpRow["RegisteredSchool"] = school;
                                                    tmpRow["RegisterdMajor"] = major;
                                                    tmpRow["Question"] = question;
                                                    tmpRow["EnrollmentMark"] = enrollmentMark;
                                                    tmpRow["EducationalLevel"] = educationalLevel;
                                                    tmpRow["Location"] = location;
                                                    tmpRow["ErrorStatus"] = contactTmpStatus;
                                                    tmpRow["Status"] = ImportInfo.Status;
                                                    dtContactTmps.Rows.Add(tmpRow);
                                                    #endregion
                                                }
                                                break;
                                        }
                                    }
                                    rowIndex++;
                                }
                            }
                        }

                        watch.Stop();
                        Console.WriteLine("Read file: " + watch.ElapsedMilliseconds);
                        watch.Reset();
                        watch.Restart();

                        // Import
                        ContactBulkImport.ImportContact(dtContacts, connection, transaction);
                        ContactBulkImport.ImportContactLevel(dtContactLevels, connection, transaction);
                        ContactBulkImport.ImportContactPhone(dtPhones, connection, transaction);
                        ContactBulkImport.ImportObjectChanges(dtObjectChanges, connection, transaction);
                        ContactBulkImport.ImportContactTmp(dtContactTmps);

                        watch.Stop();
                        Console.WriteLine("Import file: " + watch.ElapsedMilliseconds);

                        // Load redis
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        if (transaction != null)
                            transaction.Rollback(); // This will not reset IDENT_CURRENT
                    }
                    finally
                    {
                        // Update ImportStatus
                        ImportInfo.ImportStatus = 1;
                        ImportExcelRepository.Update(ImportInfo);

                        // Update Duplicate
                        UpdateDuplicate();

                        // Reload Redis
                        StaticData.LoadToRedis();
                    }
                }

                dtPhones = null;
                dtContacts = null;
                dtContactTmps = null;
                dtContactLevels = null;
                dtObjectChanges = null;
            }
            catch (Exception ex2)
            {
                Console.WriteLine(ex2.ToString());
            }
        }

        #region Utility
        public void Dispose()
        {
            ImportInfo = null;
        }
        private void UpdateDuplicate()
        {
            if (duplicateContactIds == null || duplicateContactIds.Count < 1) return;
            try
            {
                foreach (var item in duplicateContactIds.Values)
                {
                    var contact = ContactRepository.GetInfo(item.ContactId); if (contact == null) continue;

                    item.Status = contact.StatusId;
                    ContactDuplicateRepository.Update(item);

                    // Phone
                    UpdatePhone(contact.Id, new List<string> { item.ContactInfo.Mobile, item.ContactInfo.Mobile2, item.ContactInfo.Tel });

                    // Email
                    if (!item.ContactInfo.Email.IsStringNullOrEmpty())
                    {
                        if (contact.Email.IsStringNullOrEmpty()) contact.Email = item.ContactInfo.Email;
                        else if (contact.Email != item.ContactInfo.Email && contact.Email2.IsStringNullOrEmpty())
                            contact.Email2 = item.ContactInfo.Email;
                    }

                    if (contact.TypeId == (int)SourceType.CC &&
                        contact.LevelId <= (int)LevelType.L1 &&
                        (contact.StatusId == (int)StatusType.ImportExcel || contact.StatusId == (int)StatusType.HandoverToDistributor))
                    {
                        // Code
                        if (!item.ContactInfo.Code.IsStringNullOrEmpty())
                            contact.Code = item.ContactInfo.Code;

                        // Fullname
                        if (!item.ContactInfo.Fullname.IsStringNullOrEmpty())
                            contact.Fullname = item.ContactInfo.Fullname;

                        // Address
                        if (!item.ContactInfo.Address.IsStringNullOrEmpty())
                            contact.Address = item.ContactInfo.Address;

                        // ChannelId
                        if (item.ContactInfo.ChannelId > 0)
                            contact.ChannelId = item.ContactInfo.ChannelId;

                        // MajorId
                        if (item.ContactInfo.MajorId > 0)
                            contact.MajorId = item.ContactInfo.MajorId;

                        // SchoolId
                        if (item.ContactInfo.SchoolId > 0)
                            contact.SchoolId = item.ContactInfo.SchoolId;

                        // SchoolCare
                        if (item.ContactInfo.SchoolCare > 0)
                            contact.SchoolCare = item.ContactInfo.SchoolCare;

                        // EducationLevelId
                        if (item.ContactInfo.EducationLevelId > 0)
                            contact.EducationLevelId = item.ContactInfo.EducationLevelId;

                        // LocationId
                        if (item.ContactInfo.LocationId > 0)
                            contact.LocationId = item.ContactInfo.LocationId;

                        // RegisteredDate
                        if (item.ContactInfo.RegisteredDate != null)
                            contact.RegisteredDate = item.ContactInfo.RegisteredDate;

                        // Birth
                        if (item.ContactInfo.Birth != null)
                            contact.Birth = item.ContactInfo.Birth;

                        // Majors
                        var contactLevelInfo = ContactLevelInfoRepository.GetInfoByContactId(item.ContactId);
                        if (!item.ContactLevelInfo.Majors.IsStringNullOrEmpty())
                            contactLevelInfo.Majors = item.ContactLevelInfo.Majors;

                        // SchoolGrad
                        if (!item.ContactLevelInfo.SchoolGrad.IsStringNullOrEmpty())
                            contactLevelInfo.SchoolGrad = item.ContactLevelInfo.SchoolGrad;

                        // CollaboratorName
                        if (!item.ContactLevelInfo.CollaboratorName.IsStringNullOrEmpty())
                            contactLevelInfo.CollaboratorName = item.ContactLevelInfo.CollaboratorName;

                        // HaveJob
                        contactLevelInfo.HaveJob = item.ContactLevelInfo.HaveJob;

                        // EducationLevels
                        if (!item.ContactLevelInfo.EducationLevels.IsStringNullOrEmpty())
                            contactLevelInfo.EducationLevels = item.ContactLevelInfo.EducationLevels;

                        // ContactLevelInfoRepository.Update
                        ContactLevelInfoRepository.Update(contactLevelInfo);
                    }

                    // ContactRepository.Update
                    ContactRepository.Update(contact);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        private static DataTable CreateContact()
        {
            var retVal = new DataTable("Contacts");
            retVal.Columns.Add("Id");
            retVal.Columns.Add("UserId");
            retVal.Columns.Add("ConsultantUserId");
            retVal.Columns.Add("Code");
            retVal.Columns.Add("Fullname");
            retVal.Columns.Add("Birth");
            retVal.Columns.Add("Email");
            retVal.Columns.Add("Address");
            retVal.Columns.Add("Gender");
            retVal.Columns.Add("TypeId");
            retVal.Columns.Add("ChannelId");
            retVal.Columns.Add("SchoolId");
            retVal.Columns.Add("LevelId");
            retVal.Columns.Add("CollectorId");
            retVal.Columns.Add("BranchId");
            retVal.Columns.Add("StatusId");
            retVal.Columns.Add("RegisteredDate");
            retVal.Columns.Add("Notes");
            retVal.Columns.Add("ImportId");
            retVal.Columns.Add("CreatedDate");
            retVal.Columns.Add("MajorId");
            retVal.Columns.Add("Draft");
            retVal.Columns.Add("AppointmentDate");
            retVal.Columns.Add("EducationLevelId");
            retVal.Columns.Add("LocationId");
            retVal.Columns.Add("HandoverDate");
            retVal.Columns.Add("CallDate");
            retVal.Columns.Add("RecoveryDate");
            retVal.Columns.Add("ReuseDate");
            retVal.Columns.Add("IsDuplicate");
            retVal.Columns.Add("StatusCareId");
            retVal.Columns.Add("StatusMapId");
            retVal.Columns.Add("SchoolCare");
            retVal.Columns.Add("HandoverDistributorDate");
            retVal.Columns.Add("CallCount");
            retVal.Columns.Add("CallInfo");
            retVal.Columns.Add("CallAmount");
            retVal.Columns.Add("StatusMapDistributorId");
            retVal.Columns.Add("AcceptDateL1");
            retVal.Columns.Add("AcceptDateL2");
            retVal.Columns.Add("CallDateDistributor");
            retVal.Columns.Add("Email2");
            return retVal;
        }
        private static DataTable CreatePhoneTable()
        {
            var retVal = new DataTable("Phones");
            retVal.Columns.Add("ContactId");
            retVal.Columns.Add("PhoneType");
            retVal.Columns.Add("PhoneNumber");
            retVal.Columns.Add("IsPrimary");
            return retVal;
        }
        private static DataTable CreateContactLevel()
        {
            var retVal = new DataTable("ContactLevelInfos");
            retVal.Columns.Add("ContactId");
            retVal.Columns.Add("EducationLevels");
            retVal.Columns.Add("EducationLevelNotes");
            retVal.Columns.Add("CalledDate");
            retVal.Columns.Add("CollaboratorName");
            retVal.Columns.Add("HaveJob");
            retVal.Columns.Add("JobTitle");
            retVal.Columns.Add("SchoolName");
            retVal.Columns.Add("WantStudy");
            retVal.Columns.Add("WantStudyNotes");
            retVal.Columns.Add("Majors");
            retVal.Columns.Add("Appointment");
            retVal.Columns.Add("AppointmentTime");
            retVal.Columns.Add("ApointmentType");
            retVal.Columns.Add("ApointmentNotes");
            retVal.Columns.Add("SubmitedForm");
            retVal.Columns.Add("SubmitedFormNotes");
            retVal.Columns.Add("FeeReview");
            retVal.Columns.Add("FeeReviewTime");
            retVal.Columns.Add("FeeReviewNotes");
            retVal.Columns.Add("AcceptedReview");
            retVal.Columns.Add("AcceptedReviewNotes");
            retVal.Columns.Add("FeeEdu");
            retVal.Columns.Add("FeeEduNotes");
            retVal.Columns.Add("SubmitedProfile");
            retVal.Columns.Add("SubmitedProfileType");
            retVal.Columns.Add("SubmitedProfileNotes");
            retVal.Columns.Add("LumpedAdmissionId");
            retVal.Columns.Add("Completed");
            retVal.Columns.Add("CompletedTime");
            retVal.Columns.Add("CompletedNotes");
            retVal.Columns.Add("LumpedCareId");
            retVal.Columns.Add("LumpedFeeId");
            retVal.Columns.Add("LatchId");
            retVal.Columns.Add("SchoolGrad");
            retVal.Columns.Add("MajorGrad");
            retVal.Columns.Add("HaveLearnTransfer");
            return retVal;
        }
        private static DataTable CreateContactTmpTable()
        {
            var retVal = new DataTable("ContactTmps");
            retVal.Columns.Add("ContactId");
            retVal.Columns.Add("ImportId");
            retVal.Columns.Add("StudentId");
            retVal.Columns.Add("Fullname");
            retVal.Columns.Add("Birth");
            retVal.Columns.Add("Mobil1");
            retVal.Columns.Add("Mobil2");
            retVal.Columns.Add("Tel");
            retVal.Columns.Add("Email");
            retVal.Columns.Add("RegisteredSchool");
            retVal.Columns.Add("RegisterdMajor");
            retVal.Columns.Add("Question");
            retVal.Columns.Add("EnrollmentMark");
            retVal.Columns.Add("EducationalLevel");
            retVal.Columns.Add("Location");
            retVal.Columns.Add("Status");
            retVal.Columns.Add("ErrorStatus");
            return retVal;
        }
        private static DataTable CreateObjectChangeTable()
        {
            var retVal = new DataTable("ActivityObjectChanges");
            retVal.Columns.Add("ActivityId");
            retVal.Columns.Add("ObjectType");
            retVal.Columns.Add("ObjectId");
            retVal.Columns.Add("PropertyType");
            retVal.Columns.Add("PropertyValueInt");
            retVal.Columns.Add("PropertyValueString");
            retVal.Columns.Add("PropertyValueDateTime");
            retVal.Columns.Add("ChangedDate");
            return retVal;
        }
        private static void UpdatePhone(int contactId, List<string> mobiles)
        {
            if (mobiles.IsNullOrEmpty()) return;
            var listPhones = PhoneRepository.GetByContact(contactId) ?? new List<PhoneInfo>();
            foreach (var mobile in mobiles
                .Where(c => !c.IsStringNullOrEmpty())
                .Where(c => !listPhones.Exists(p => p.PhoneNumber.Equals(c))))
            {
                if (!listPhones.Exists(c => c.PhoneType.ToInt32() == (int)PhoneType.HomePhone))
                {
                    var phone = new PhoneInfo
                                    {
                                        IsPrimary = 0,
                                        PhoneNumber = mobile,
                                        ContactId = contactId,
                                        PhoneType = ((int)PhoneType.HomePhone).ToString(),
                                    };
                    PhoneRepository.Create(phone);
                    listPhones.Add(phone);
                }
                if (!listPhones.Exists(c => c.PhoneType.ToInt32() == (int)PhoneType.MobilePhone))
                {
                    var phone = new PhoneInfo
                    {
                        IsPrimary = 0,
                        PhoneNumber = mobile,
                        ContactId = contactId,
                        PhoneType = ((int)PhoneType.MobilePhone).ToString(),
                    };
                    PhoneRepository.Create(phone);
                    listPhones.Add(phone);
                }
                if (!listPhones.Exists(c => c.PhoneType.ToInt32() == (int)PhoneType.Tel))
                {
                    var phone = new PhoneInfo
                    {
                        IsPrimary = 0,
                        PhoneNumber = mobile,
                        ContactId = contactId,
                        PhoneType = ((int)PhoneType.Tel).ToString(),
                    };
                    PhoneRepository.Create(phone);
                    listPhones.Add(phone);
                }
            }
        }
        private bool IsInternalDuplicate(string mobile1, string mobible2, string tel, string email)
        {
            if (!string.IsNullOrEmpty(mobile1) && internalPhoneAndEmails.Contains(mobile1)) return true;
            if (!string.IsNullOrEmpty(mobible2) && internalPhoneAndEmails.Contains(mobible2)) return true;
            if (!string.IsNullOrEmpty(tel) && internalPhoneAndEmails.Contains(tel)) return true;
            if (!string.IsNullOrEmpty(email) && internalPhoneAndEmails.Contains(email)) return true;
            return false;
        }
        private static ContactError Validate(string mobile1, string mobile2, string tel, string email)
        {
            if (string.IsNullOrEmpty(mobile1) || !Util.ValidateMobile(mobile1))
            {
                return ContactError.MobilePhoneFormat;
            }

            if (!string.IsNullOrEmpty(mobile2) && !Util.ValidateMobile(mobile2))
            {
                return ContactError.MobilePhoneFormat;
            }
            if (!string.IsNullOrEmpty(tel) && !Util.ValidateMobile(tel))
            {
                return ContactError.HomePhoneFormat;
            }
            if (!string.IsNullOrEmpty(email) && !Util.ValidateFormat(email, ImportConfig.EmailFormat))
            {
                return ContactError.EmailFormat;
            }
            return ContactError.None;
        }
        public int GetIdentity(string tableName, SqlConnection connection, SqlTransaction transaction)
        {
            int identity;

            // Get the last customer identity
            using (var sqlCommand = connection.CreateCommand())
            {
                sqlCommand.CommandText = string.Format("SELECT IDENT_CURRENT('{0}')", tableName);
                sqlCommand.Transaction = transaction;
                identity = Convert.ToInt32(sqlCommand.ExecuteScalar());
            }

            return identity;
        }
        #endregion
    }
}
